﻿Imports System.Data
Imports System.Data.OleDb
Imports System.IO
Imports System.Data.SqlClient

Public Class CargarArchivos
    Public Shared Function ChecarExtension(ByVal extension As String) As Boolean

        Select Case extension.ToLower()
            Case ".xlsx"
                Return True
            Case Else
                Return False
        End Select

    End Function
    Public Shared Sub Cargar(ByVal archivo As FileUpload, ByVal instancia As HttpRequest, ByVal tabla As GridView)
        Dim _carpeta As String = "\Uploaded Folder\"
        Dim _directorioGral As String = instancia.PhysicalApplicationPath

        Try
            If archivo.HasFile Then
                Dim _extension As String = Path.GetExtension(archivo.FileName)
                If ChecarExtension(_extension) Then
                    Dim _directorioParaGuardar As String = _directorioGral + _carpeta + archivo.FileName
                    '----------------------Eliminar Archivos Existentes------------------------------
                    EliminarArchivos(instancia)

                    '-----------------------Guardar nuevo archivo------------------------------
                    archivo.SaveAs(_directorioParaGuardar)

                    tabla.DataSource = MostrarExcel(_directorioParaGuardar)
                    tabla.DataBind()


                End If

            Else

            End If
        Catch ex As Exception
            Throw New Exception("Error al cargar el archivo ", ex)
        End Try

    End Sub
    Public Shared Sub EliminarArchivos(ByVal instancia As HttpRequest)

        Dim _carpeta As String = "\Uploaded Folder\"
        Dim _directorioGral As String = instancia.PhysicalApplicationPath + _carpeta

        Dim directorio As New DirectoryInfo(_directorioGral)
        Dim archivos As FileInfo() = directorio.GetFiles()
        Dim archivo As FileInfo

        For Each archivo In archivos
            archivo.Delete()
        Next

    End Sub
    Public Shared Function MostrarExcel(ByVal ruta As String) As DataTable

        Dim _cadenaconexion As String = String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=Excel 12.0", ruta)
        Dim _oledbConn As New OleDbConnection(_cadenaconexion)
        Dim dt As New DataTable()

        Try
            _oledbConn.Open()
            Dim _cmd As OleDbCommand = New OleDbCommand("SELECT * FROM [Hoja1$]", _oledbConn)
            Dim _oleda As OleDbDataAdapter = New OleDbDataAdapter()
            _oleda.SelectCommand = _cmd

            _oleda.Fill(dt)

            _oleda.Dispose()
            _cmd.Dispose()
        Catch ex As Exception

        Finally

            _oledbConn.Close()
            _oledbConn.Dispose()
        End Try
        Return dt
    End Function
End Class
